import pandas as pd
from copy import deepcopy
import re
from Extend.SqlHelper import SqlServer


class ExcelRead:
    def __init__(self, file_path, sheet_page=0, sheet_name=None):
        if sheet_name:
            self._sheet = pd.read_excel(file_path, sheet_name)
        else:
            self._sheet = pd.read_excel(file_path, sheet_page)
        self._result_dicts = []

    # 读取文件，内容，需要有数据开始行，和需要的列，需要的列以字典形式呈现，方便结果的展示
    def readContent(self, value_column, content_row=0, subsection=None, end_word=None):
        data_frame = pd.DataFrame(self._sheet)
        loop = 0
        sub_list = []
        for x in data_frame.values:
            loop += 1
            if loop < content_row: continue
            s = str(x[0])
            if subsection is not None and str(x[0]).startswith(subsection):
                self._result_dicts.append(deepcopy(sub_list))
                sub_list = []
                continue
            elif end_word is not None and str(x[0]).startswith(end_word):
                break
            res = self.toNewDictionary(x, value_column)
            if res is not None:
                sub_list.append(res)
        if len(sub_list) > 0:
            self._result_dicts.append(deepcopy(sub_list))

    # 将符合条件的数据加入字典当中
    def toNewDictionary(self, item, kwargs):
        result = {}
        for x, y in kwargs.items():
            result[x] = item[y] if str(item[y]) != 'nan' else None
        for x in result.values():
            if x is not None:
                break
        else:
            return None
        return result

    # 用户返会列表
    @property
    def result(self):
        return self._result_dicts


if __name__ == '__main__':

    file = r'F:/工程单0629.xlsx'
    items = {'contractno': 2, 'Quntity': 8}
    ex = ExcelRead(file)
    ex.readContent(items, content_row=0)
    new = []
    sql_link = SqlServer('172.16.200.5', 'sa', 'Liweijia456', 'EasyFAS_Lwj_Release')
    # 第一步，更改套数
    # sql = "update C_OrderGoodsDetail set Quantity=Quantity*{} where OrderId in (select id from C_Order where ContractNo='{}')"
    # for x in ex.result[0]:
    #     new.append(sql.format(x['Quntity'], x['contractno']))
    # print([x['contractno'] for x in ex.result[0]])
    # # print(new)
    # sql_link.commitAll(new)

#     sql = """select OrderNo,ProductName,Name,color,sum(Quantity) Quantity,CutWidth,CutLength,Width,Length,Height from C_Order o
# inner join C_OrderGoodsDetail ogd on ogd.OrderId = o.Id
# where ContractNo = '{}' group by OrderNo,ProductName,Name,color,CutWidth,CutLength,Width,Length,Height
# """
#     results = ''
#     for x in ex.result[0]:
#         result = sql_link.getAllData(sql.format(x['contractno']))
#         for i in result:
#             i['Quantity'] = i['Quantity'] * x['Quntity']
#             results += ",".join([str(x) for x in i.values()]) + '\n'
#     print(results)
# 第二步 修改数据
# 还原数量
    sql0 = "update C_OrderGoodsDetail set Quantity=Quantity/{} where OrderId in (select id from C_Order where ContractNo='{}')"
# # 更改为工程单
# sql1 = "update C_Order set Ploidy={0},OrderType=6,ProjectBatchId=37,ProjectId=18 where  ContractNo='{1}'"
# # 查询id
    sql2 = "select id from C_Order where ContractNo='{}'"
# 增加房间
    sql3 = "insert into [C_ProjectOrder](ProjectOrderId,RoomSeq) values({},{})"
    sqls = []
    orders = []
    for x in ex.result[0]:
        # if x['unit'] != 1:
        sql_link.changeData(sql0.format(x['Quntity'], x['contractno']))
        # sql_link.changeData(sql1.format(x['Quntity'], x['contractno']))
        result = sql_link.getOneData(sql2.format(x['contractno']))
        if result:
            for x in range(x['Quntity']):
                x += 1
                sqls.append(sql3.format(result['id'], x))
    sql_link.commitAll(sqls)
    # print(sqls)
# for x in range(16):
#     x += 1
#     sqls.append(sql3.format(131382, x))
# sql_link.commitAll(sqls)
# print(sqls)
# sql = "select * from M_Material where Code = '{}'"
# result = DataFactory.removeNoneData(ex.result, 'all_area')
# result = DataFactory.splitMerge(result, 'room')
